package com.ruoyi.shenji.utils;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

@Configuration
public class DatabaseUtils {

    @Autowired
    private DataSource dataSource; // 这个将会自动注入由Spring管理的数据源

    public void executeInsert(String insertTableSQL) {
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {

            statement.executeUpdate(insertTableSQL);
            System.out.println("Table Inserted successfully.");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void executeDelete(String delTableSQL) {
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {

            statement.executeUpdate(delTableSQL);
            System.out.println("Table Inserted successfully.");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void executeCreate(String createTableSQL) {
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {

            statement.executeUpdate(createTableSQL);
            System.out.println("Table created successfully.");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void executeDrop(String dropTableSQL) {
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {

            statement.executeUpdate(dropTableSQL);
            System.out.println("Table drop successfully.");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public List<LinkedHashMap<String, Object>> executeSelect(String selectTableSQL, int pageNum, int pageSize) {
        // 假设使用的数据库是MySQL，分页语法为LIMIT offset, limit
        StringBuilder sqlBuilder = new StringBuilder(selectTableSQL);
        sqlBuilder.append(" LIMIT ").append((pageNum - 1) * pageSize).append(", ").append(pageSize);
        List<LinkedHashMap<String, Object>> result = new ArrayList<>();
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {

            ResultSet resultSet = statement.executeQuery(sqlBuilder.toString());
            System.out.println("Select executed successfully.");

            // 获取列名
            int columnCount = resultSet.getMetaData().getColumnCount();
            String[] columnNames = new String[columnCount];
            for (int i = 1; i <= columnCount; i++) {
                columnNames[i - 1] = resultSet.getMetaData().getColumnName(i);
            }

            // 处理查询结果，并放入List<Map<String, Object>>
            while (resultSet.next()) {
                LinkedHashMap<String, Object> row = new LinkedHashMap<>();
                for (String columnName : columnNames) {
                    row.put(columnName, resultSet.getObject(columnName));
                }
                result.add(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public int executeTotalCount(String selectTableSQL) {
        int totalCount = 0;
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {

            // 添加计数查询语句
            String countSQL = "SELECT COUNT(*) AS total FROM (" + selectTableSQL + ") AS subquery";
            ResultSet resultSet = statement.executeQuery(countSQL);

            if (resultSet.next()) {
                totalCount = resultSet.getInt("total");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return totalCount;
    }
}